package com.wxtf.platform.main.report;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.net.URLDecoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.FormatUtil;

/**
 * <li>Title: QuarterlyUsageReport.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.report</li>
 * <li>Description: 季药具领用情况报告单</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Aug 21, 2012 10:29:02 AM</li>
 * 
 * @author zhanqiulin
 * @version 1.0.0.0
 * 
 */
public class QuarterlyUsageReport extends LogicBase implements CommonsCallProvider{
	private static final Log log = LogFactory.getLog(QuarterlyUsageReport.class);

	public QuarterlyUsageReport(String jsonArray){

	}
	
	@Override
	public Object run() {
		Object msg = "";
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
				return msg;
			} else if("save".equals(this.getMethodName())) {
				msg = this.insert(this.getLogicData());
			} else if("update".equals(this.getMethodName())) {
				msg = this.update(this.getLogicData());
			} else if("delete".equals(this.getMethodName())) {
				msg = this.delete(this.getLogicData());
			} else if("oneKeyReport".equals(this.getMethodName())) {
				msg = this.oneKeyReport(this.getLogicData());
			} else if("revoke".equals(this.getMethodName())) {
				msg = this.revoke(this.getLogicData());
			} else if("alert".equals(this.getMethodName())) {
				msg = this.alert(this.getLogicData());
			} else if("download".equals(this.getMethodName())) {
				msg = this.download(this.getLogicData());
			} else {
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(e, e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	String[] fields = {"tmapm_id","tbe_id","org_id","tbe_name","tbe_address","tbe_age","tbe_position","tbe_qualification","tbfi_children_num","tmad_name","tmapm_begin_time","tmapm_reason","tmapm_last_medicine","tmapm_is_fail","tmapm_fail_reason","tmad_unit","tmapd_num","tmapm_style","reserve1","reserve2","reserve3","reserve4","reserve5","create_by","create_time","last_modify_by","last_modify_time"
	};
	String[] fields1 = {
			"tmamr_id","org_id","tmamr_matron_num","tmamr_women_fertility_num",
			"tmamr_ligation_num","tmamr_sheung_wan_num","tmamr_skinburied_num","tmamr_keep_certificate_num","tmamr_thismonth_birth_num",
			"tmamr_unmarry_num","tmamr_widowed_num","tmamr_barrenness_num","tmamr_menopause_num","tmamr_director","reserve1","reserve2",
			"reserve3","reserve4","reserve5","create_by","create_time","last_modify_by","last_modify_time","tmamr_date"};
	@Override
	public Object select(Object jsonArray) throws Exception {
		int tmamr_thismonth_birth_nums = 0,tmamr_unmarry_num = 0, tmamr_widowed_num = 0, tmamr_barrenness_num = 0, tmamr_menopause_num = 0;
		JSONObject result = new JSONObject();
		JSONArray tabledata = new JSONArray();
		JSONArray array = JSONArray.fromObject(jsonArray);
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		String quarterly = "";
		String year = "";
		StringBuffer buf = new StringBuffer();
		buf.append("select department_id from t_department where dparent_id = '").append(user.getCurOrgId()).append("'");
		List<?> orgids = AccessDataBaseUtil.query4List(buf.toString(), null);
		buf.setLength(0);
		
		buf.append("'").append(user.getCurOrgId()).append("'");
		for(int i = 0; i < orgids.size(); i++){
			buf.append(",'").append(orgids.get(i)).append("'");
		}
		String ids = buf.toString();
		buf.setLength(0);
		String reportTime = FormatUtil.getCurentDate();
		List<Object[]> list;
		if("".equals(ids) && ids == null){
			return "";
		}else{
			String[] idArray = ids.split(",");
			Object[] objs;
			JSONObject orgtmad = new JSONObject();
			JSONArray tmaUses = new JSONArray();
			JSONObject tmaUse;
			String mindata = "";
			String maxdata = "";
			if(array.size() > 0) {
				JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
				quarterly = jsonObject.getString("quarterly");//季度
				year = jsonObject.getString("year");//年度
				
				if(StringUtils.isBlank(quarterly)){
					//计算当前季度
					Calendar cal = Calendar.getInstance();
					year = String.valueOf(cal.get(Calendar.YEAR));
					int m = cal.get(Calendar.MONTH)+1;
					if(m < 4){
						quarterly = "一";
					}else if(m >= 4 && m < 7){
						quarterly = "二";
					}else if(m >= 7 && m < 10){
						quarterly = "三";
					}else
						quarterly = "四";
				}
				String d = FormatUtil.getCurentDate().split("-")[2];
				if("一".equals(quarterly)){
					reportTime = year+"-03-"+d;
				}else if("二".equals(quarterly)){
					reportTime = year+"-06-"+d;
				}else if("三".equals(quarterly)){
					reportTime = year+"-09-"+d;
				}else{
					reportTime = year+"-12-"+d;
				}
			}
			//查询当季报表是否生成
		 	List<Object[]> res1 = AccessDataBaseUtil.query("select tmaqu_time,tmaqu_id from tma_quarterly_usage where org_id = '"+user.getCurOrgId()+"' and tmaqu_time like '%"+year+"%' and tmaqu_quarterly = '"+quarterly+"'", null);
		 	//如果不是最下级机构	
			if(idArray.length > 1){
				//已生成当月报表
				if(res1.size() >0){
					reportTime = String.valueOf(res1.get(0)[0]);
				//未生成当月报表	
				}
				buf.append("select n.tid,m.qid,n.tkind,m.qnum,n.orgname,n.orgid from (select a.CAPTION as orgname,b.tmad_id as tid,b.tmad_kind as tkind,")
				.append("b.tmad_name as tname,a.ordernum as onum,a.DEPARTMENT_ID as orgid,a.levels as lev from t_department a,tma_dictionary b where b.tmad_kind ")
				.append("not in('105-1','105-3','105-4','105-5') and b.tmad_status='0' and b.reserve1 ='1' and a.DEPARTMENT_ID in (")
				.append(ids).append(")) n left join (select tmad_id as tid,tmaqu_id as qid,tmaqu_peonum as qnum,org_id as oid from tma_quarterly_usage ")
				.append("where tmaqu_time like '").append(year).append("%' and tmaqu_quarterly = '").append(quarterly)
				.append("') m on n.tid = m.tid and n.orgid=m.oid order by n.lev,n.onum,n.orgid,n.tkind desc,n.tid");
				res1 = AccessDataBaseUtil.query(buf.toString(), null);
				if(res1 != null){
					String temp = "";
					Object[] o;
					for(int i = 0 ; i < res1.size(); i++){
						o = res1.get(i);
						tmaUse = new JSONObject();
						tmaUse.put("tmadid", o[0]);
						tmaUse.put("quid", o[1]);
						tmaUse.put("kind", o[2]);
						tmaUse.put("usenum", o[3]);
						if(!temp.equals(o[5])){
							temp = o[5].toString();
							if(i > 0){
								orgtmad.put("values", tmaUses);
								tabledata.add(orgtmad);
							}
							tmaUses = new JSONArray();
							orgtmad = new JSONObject();
							Boolean isRepQ = false;
							if(StringUtils.isNotBlank(String.valueOf(o[1]))){
								isRepQ = true;
							}
							orgtmad.put("isRepQ", isRepQ);
							orgtmad.put("orgid", o[5]);
							orgtmad.put("orgname", o[4]);
						}
						tmaUses.add(tmaUse);
						if(i == res1.size()-1){
							orgtmad.put("values", tmaUses);
							tabledata.add(orgtmad);
						}
					}
				}
			}else if(idArray.length == 1){
			//如果是最下级机构
				if(res1.size() > 0){
					reportTime = String.valueOf(res1.get(0)[0]);
					res1 = AccessDataBaseUtil.query("select a.tmad_id,b.tmaqu_id,a.tmad_kind,b.tmaqu_peonum from tma_dictionary a left join tma_quarterly_usage b on a.tmad_id = b.tmad_id and b.org_id = '"+user.getCurOrgId()+"' and b.tmaqu_time like '%"+year+"%' and tmaqu_quarterly = '"+quarterly+"' where a.tmad_kind not in('105-1','105-3','105-4','105-5') and a.tmad_status='0' and a.reserve1 ='1' order by a.tmad_kind desc, a.tmad_id", null);
					Object[] o;
					orgtmad = new JSONObject();
					tmaUses = new JSONArray();
					for(int i = 0; i < res1.size(); i++){
						o = res1.get(i);
						tmaUse = new JSONObject();
						tmaUse.put("tmadid", o[0]);
						tmaUse.put("quid", o[1]);
						tmaUse.put("kind", o[2]);
						tmaUse.put("usenum", o[3]);
						tmaUses.add(tmaUse);
					}
					orgtmad.put("isRepQ", true);
				}else{
			//没有生成当季报表
					String quTemp = "";
					int yearTemp = Integer.parseInt(year);
					if("一".equals(quarterly)){
						mindata = yearTemp + "-01";
						maxdata = yearTemp + "-04";
						quTemp = "四";
						yearTemp = yearTemp - 1;
					}else if("二".equals(quarterly)){
						mindata = yearTemp + "-04";
						maxdata = yearTemp + "-07";
						quTemp = "一";
					}else if("三".equals(quarterly)){
						mindata = yearTemp + "-07";
						maxdata = yearTemp + "-10";
						quTemp = "二";
					}else if("四".equals(quarterly)){
						mindata = yearTemp + "-10";
						maxdata = (yearTemp + 1) + "-01";
						quTemp = "三";
					}
					List<Object> l = AccessDataBaseUtil.query4List("select tmaqu_time from tma_quarterly_usage where org_id = '"+user.getCurOrgId()+"' and tmaqu_time like '%"+yearTemp+"%' and tmaqu_quarterly = '"+quTemp+"'", null);
					if(l.size() > 0){
						Object o = l.get(0);
						mindata = String.valueOf(o);
					}
					orgtmad = new JSONObject();
					buf.setLength(0);
					buf.append("select a.tmad_id,a.tmad_kind,count(e.name)")
					.append(" from tma_dictionary a left join (select b.tmad_id as name from tma_provide_detail b inner join tma_provide_main c on b.tmapm_id = c.tmapm_id and c.org_id = ")
					.append(idArray[0]).append(" and c.tmapm_time >= '").append(mindata).append("' and c.tmapm_time < '").append(maxdata).append("' and c.reserve2 = '1' group by b.tmad_id,c.tbe_id ) e on a.tmad_id = e.name where a.tmad_kind not in('105-1','105-3','105-4','105-5') and a.tmad_status='0' and a.reserve1='1' group by a.tmad_id,a.tmad_kind order by a.tmad_kind desc,a.tmad_id");
					list = AccessDataBaseUtil.query(buf.toString(), null);
					tmaUses = new JSONArray();
					for(int j = 0; j < list.size(); j++){
						objs = list.get(j);
						tmaUse = new JSONObject();
						tmaUse.put("tmadid", objs[0]);
						tmaUse.put("kind", objs[1]);
						tmaUse.put("usenum", objs[2]);
						tmaUses.add(tmaUse);
					}
					orgtmad.put("isRepQ", false);
				}
				orgtmad.put("orgid", idArray[0]);
				orgtmad.put("orgname", user.getCurOrgName());
				orgtmad.put("values", tmaUses);
				tabledata.add(orgtmad);
			}
			int yearTemp = Integer.parseInt(year);
			String endDate = "";
			if("一".equals(quarterly)){
				mindata = yearTemp + "-01";
				maxdata = yearTemp + "-04";
				endDate = yearTemp + "-03";
			}else if("二".equals(quarterly)){
				mindata = yearTemp + "-04";
				maxdata = yearTemp + "-07";
				endDate = yearTemp + "-06";
			}else if("三".equals(quarterly)){
				mindata = yearTemp + "-07";
				maxdata = yearTemp + "-10";
				endDate = yearTemp + "-09";
			}else if("四".equals(quarterly)){
				mindata = yearTemp + "-10";
				maxdata = (yearTemp + 1) + "-01";
				endDate = yearTemp + "-12";
			}
			buf.setLength(0);
			buf.append("select a.org_id,a.tmamr_matron_num,a.tmamr_women_fertility_num,a.tmamr_ligation_num,a.tmamr_sheung_wan_num,a.tmamr_skinburied_num,")
			.append("a.tmamr_keep_certificate_num,a.tmamr_thismonth_birth_num,a.tmamr_unmarry_num,a.tmamr_widowed_num,a.tmamr_barrenness_num,")
			.append("a.tmamr_menopause_num,a.tmamr_id from tma_month_usage_report a where a.tmamr_date like '%").append(endDate).append("%' and a.org_id in (")
			.append(ids).append(")");
			list = AccessDataBaseUtil.query(buf.toString(), null);
			if(list.size() > 0){
				for(int i = 0; i < tabledata.size(); i++){
					orgtmad = tabledata.getJSONObject(i);
					for(int j = 0; j < list.size(); j++){
						Object[] o = list.get(j);
						if(String.valueOf(o[0]).equals(orgtmad.get("orgid"))){
							orgtmad.put("tmamr_matron_num", o[1]);
							orgtmad.put("tmamr_women_fertility_num", o[2]);
							orgtmad.put("tmamr_ligation_num", o[3]);
							orgtmad.put("tmamr_sheung_wan_num", o[4]);
							orgtmad.put("tmamr_skinburied_num", o[5]);
							orgtmad.put("tmamr_keep_certificate_num", o[6]);
							orgtmad.put("tmamr_id", o[12]);
							tmamr_thismonth_birth_nums += Integer.parseInt(String.valueOf(o[7]));
							tmamr_unmarry_num += Integer.parseInt(String.valueOf(o[8]));
							tmamr_widowed_num += Integer.parseInt(String.valueOf(o[9]));
							tmamr_barrenness_num += Integer.parseInt(String.valueOf(o[10]));
							tmamr_menopause_num += Integer.parseInt(String.valueOf(o[11]));
						}
					}
				}
			}
			buf.setLength(0);
		}
		result.put("gridData", tabledata);
		result.put("tmamr_thismonth_birth_nums", tmamr_thismonth_birth_nums);
		result.put("tmamr_unmarry_num", tmamr_unmarry_num);
		result.put("tmamr_widowed_num", tmamr_widowed_num);
		result.put("tmamr_barrenness_num", tmamr_barrenness_num);
		result.put("tmamr_menopause_num", tmamr_menopause_num);
		result.put("quarterly", quarterly);
		result.put("year", year);
		result.put("reportTime", reportTime);
		return result.toString();
	}

	@Override
	public Object insert(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
//		String orgid = user.getCurOrgId();
//		synchronized(orgid){
			List<String> list = new ArrayList<String>();
			JSONArray array = JSONArray.fromObject(jsonArray);
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String quarterly = jsonObject.getString("quarterly");
			String reportTime = jsonObject.getString("reportTime");
			JSONArray repArray = jsonObject.getJSONArray("repArray");
			String mrArray = jsonObject.getString("mrArray");
			StringBuffer buff = new StringBuffer();
			String curTime = FormatUtil.getCurentDateTime();
			JSONObject obj;
			List<Object[]> result = new ArrayList<Object[]> ();
			result = AccessDataBaseUtil.query("select tmaqu_id,org_id from tma_quarterly_usage where TMAQU_QUARTERLY='"+quarterly+"' and TMAQU_TIME like '%"+reportTime.split("-")[0]+"%' and org_id in('"+user.getOrgPId(user.getCurOrgId())+"','"+user.getCurOrgId()+"') group by org_id", null);
			if(result.size() > 0){
				Object[] o = result.get(0);
				if(o[1].equals(user.getOrgPId(user.getCurOrgId()))){
					//return "\"上级已上报，不能再报\"";
				}else{
				//	return "\"本季已上报，不能再报\"";
				}
			}
			String id = "";
			if("一".equals(quarterly)){
				id = StringUtils.left(reportTime, 4) + "1";
			}else if("二".equals(quarterly)){
				id = StringUtils.left(reportTime, 4) + "2";
			}else if("三".equals(quarterly)){
				id = StringUtils.left(reportTime, 4) + "3";
			}else{
				id = StringUtils.left(reportTime, 4) + "4";
			}
			id += StringUtils.right(user.getCurOrgId(), 10);
			for(int i = 0; i < repArray.size(); i++){
				obj = repArray.getJSONObject(i);
				buff.append("insert into tma_quarterly_usage(TMAQU_ID,ORG_ID,TMAD_ID,TMAD_KIND,TMAQU_PEONUM,TMAQU_TIME,TMAQU_QUARTERLY,RESERVE1,")
				.append("RESERVE2,RESERVE3,RESERVE4,RESERVE5,CREATE_BY,CREATE_TIME,LAST_MODIFY_BY,LAST_MODIFY_TIME)")
				.append("values('").append(id+StringUtils.right(obj.getString("id"), 10)).append("','").append(user.getCurOrgId())
				.append("','").append(obj.getString("id")).append("','").append(obj.getString("kind")).append("','")
				.append(obj.getString("value")).append("','").append(reportTime).append("','").append(quarterly).append("','','','','','','")
				.append(user.getUserId()).append("','").append(curTime).append("','").append(user.getUserId()).append("','")
				.append(curTime).append("')");
				list.add(buff.toString());
				buff.setLength(0);
			}
			JSONArray arr1 = jsonObject.getJSONArray("arr1");
			JSONArray arr2 = jsonObject.getJSONArray("arr2");
			for(int i = 0; i < arr1.size(); i++){
				obj = arr1.getJSONObject(i);
				buff.append("update tma_month_usage_report set ").append(obj.getString("kind")).append("='").append(obj.getString("value"))
				.append("',LAST_MODIFY_BY='").append(user.getUserId()).append("',LAST_MODIFY_TIME='").append(curTime)
				.append("' where tmamr_id='").append(obj.getString("id")).append("'");
				list.add(buff.toString());
				buff.setLength(0);
			}
			for(int i = 0; i < arr2.size(); i++){
				obj = arr2.getJSONObject(i);
				buff.append("update tma_quarterly_usage set TMAQU_PEONUM='").append(obj.getString("value"))
				.append("',LAST_MODIFY_BY='").append(user.getUserId()).append("',LAST_MODIFY_TIME='").append(curTime)
				.append("' where TMAQU_ID='").append(obj.getString("id")).append("'");
				list.add(buff.toString());
				buff.setLength(0);
			}
			if(!StringUtils.isBlank(mrArray)){
				autoCreateMonthReport(jsonObject);
			}
			AccessDataBaseUtil.executeSQLUpdate(list);
			return "\"保存成功\"";
//		}
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		List<String> list = new ArrayList<String>();
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String quarterly = jsonObject.getString("quarterly");
		String reportTime = jsonObject.getString("reportTime");
		String mrArray = jsonObject.getString("mrArray");
		StringBuffer buff = new StringBuffer();
		String curTime = FormatUtil.getCurentDateTime();
		JSONObject obj;
		List<Object[]> result = new ArrayList<Object[]> ();
		result = AccessDataBaseUtil.query("select tmaqu_id,org_id from tma_quarterly_usage where TMAQU_QUARTERLY='"+quarterly+"' and TMAQU_TIME like '%"+reportTime.split("-")[0]+"%' and org_id in('"+user.getOrgPId(user.getCurOrgId())+"','"+user.getCurOrgId()+"') group by org_id", null);
		if(result.size() > 0){
			Object[] o = result.get(0);
			if(o[1].equals(user.getOrgPId(user.getCurOrgId()))){
				return "\"上级已上报，不能再次修改\"";
			}
		}
		JSONArray arr1 = jsonObject.getJSONArray("arr1");
		JSONArray arr2 = jsonObject.getJSONArray("arr2");
		for(int i = 0; i < arr1.size(); i++){
			obj = arr1.getJSONObject(i);
			buff.append("update tma_month_usage_report set ").append(obj.getString("kind")).append("='").append(obj.getString("value"))
			.append("',LAST_MODIFY_BY='").append(user.getUserId()).append("',LAST_MODIFY_TIME='").append(curTime)
			.append("' where tmamr_id='").append(obj.getString("id")).append("'");
			list.add(buff.toString());
			buff.setLength(0);
		}
		for(int i = 0; i < arr2.size(); i++){
			obj = arr2.getJSONObject(i);
			buff.append("update tma_quarterly_usage set TMAQU_PEONUM='").append(obj.getString("value"))
			.append("',LAST_MODIFY_BY='").append(user.getUserId()).append("',LAST_MODIFY_TIME='").append(curTime)
			.append("' where TMAQU_ID='").append(obj.getString("id")).append("'");
			list.add(buff.toString());
			buff.setLength(0);
		}
		if(!StringUtils.isBlank(mrArray)){
			autoCreateMonthReport(jsonObject);
		}
		AccessDataBaseUtil.executeSQLUpdate(list);
		return "\"修改成功\"";
	}

	@Override
	public Object delete(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String id = jsonObject.getString("ids");
		String[] ids = id.split(",");
		sql = "delete from tma_dictionary where tmad_id = ?";
		
		AccessDataBaseUtil.deleteByIds(sql, ids);
		return "\"删除成功\"";
	}

	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
	/**
	 * 
	 * 描述：下载报表
	 * @param jsonArray
	 * @return Object
	 * @throws Exception
	 * @CreateOn Sep 13, 2012 12:30:36 PM
	 * @author bai_xin
	 */
	public Object download(String jsonArray) throws Exception {
		//XML文件所在真实路径
		String url = this.getRequest().getSession().getServletContext().getRealPath("/jsp/app/xls/");
		//模板文件所在位置
		String model = url + "/model/jyjsyffqkbb.xls";
		//装填好的Excel文件所在位置
		String file = url + "/file/jyjsyffqkbb.xls";
		//文件下载路径（最后返回的路径）
		String rfile = "jsp/app/xls/file/jyjsyffqkbb.xls";
		//获取页面传入的logicdata
		JSONArray array = JSONArray.fromObject(jsonArray);
		//判断如果有值则继续进行，没有则返回相应错误提示信息
		if(array.size() > 0) {
			UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String year = jsonObject.getString("year");
			String quarterly = jsonObject.getString("quarterly");
			int tdCount = jsonObject.getInt("td_count");
			int tmaTitleTdCount = jsonObject.getInt("tmaTitle_td_count");
			JSONArray data = jsonObject.getJSONArray("data");
			JSONArray kind = jsonObject.getJSONArray("kind");
			JSONArray tmaTitle = jsonObject.getJSONArray("tmaTitle");
			
			String tmamr_thismonth_birth_nums = jsonObject.getString("tmamr_thismonth_birth_nums");
			String tmamr_unmarry_num = jsonObject.getString("tmamr_unmarry_num");
			String tmamr_widowed_num = jsonObject.getString("tmamr_widowed_num");
			String tmamr_barrenness_num = jsonObject.getString("tmamr_barrenness_num");
			String tmamr_menopause_num = jsonObject.getString("tmamr_menopause_num");
			String dwfzr = jsonObject.getString("dwfzr");
			String tbr = jsonObject.getString("tbr");
			String tbrq_nian = jsonObject.getString("tbrq_nian");
			String tbrq_yue = jsonObject.getString("tbrq_yue");
			String tbrq_ri = jsonObject.getString("tbrq_ri");
			
			String reportName = year + "年第" + quarterly + "季度避孕药具使用发放报表";
			String orgName = "填报单位:" + user.getCurOrgName();
			//通过模板文件复制一份文件
			File ffrom = new File(model);
			File fto = new File(file);
			Common.copyFile(ffrom, fto);
			//判断是否有部门数据
			if(data != null && data.size() > 0){
				//打开刚刚生成好的Excel文件并生成相应的POI类对象
				POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(fto));
				HSSFWorkbook book = new HSSFWorkbook(filein);
				HSSFSheet sheet = book.getSheet("避孕药具使用发放报表");
				
				/*
				 * 定义各种单元格的样式风格（HSSFCellStyle）
				 * */
				//定义字体
				HSSFFont styleFont = book.createFont();
				//设置表格默认列宽度
				sheet.setDefaultColumnWidth(5);
				//设置第0列（部门名称列）的宽度
				sheet.setColumnWidth(0, 4000);
				//定义：reportNameStyle（表格标题单元格的样式）
				HSSFCellStyle reportNameStyle = book.createCellStyle();
				styleFont.setFontName("黑体");
				styleFont.setFontHeightInPoints((short) 20);//设置字体大小
				styleFont.setBoldweight((short)600);
				reportNameStyle.setFont(styleFont);
				reportNameStyle.setAlignment(CellStyle.ALIGN_CENTER);
				reportNameStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				//定义：orgNameStyle（上报单位单元格的样式）
				HSSFCellStyle orgNameStyle = book.createCellStyle();
				styleFont = book.createFont();
				styleFont.setFontName("宋体");
				styleFont.setFontHeightInPoints((short) 14);//设置字体大小
				orgNameStyle.setFont(styleFont);
				orgNameStyle.setAlignment(CellStyle.ALIGN_LEFT);
				orgNameStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				//定义：stickStyle（前7列和后两列固定列样式）
				HSSFCellStyle stickStyle = book.createCellStyle();
				styleFont = book.createFont();
				styleFont.setFontName("宋体");
				styleFont.setFontHeightInPoints((short) 12);//设置字体大小
				stickStyle.setFont(styleFont);
				stickStyle.setAlignment(CellStyle.ALIGN_CENTER);
				stickStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				stickStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
				stickStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
				stickStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
				stickStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
				stickStyle.setWrapText(true);
				//定义：specialStyle（中间N列特殊样式）
				HSSFCellStyle specialStyle = book.createCellStyle();
				styleFont = book.createFont();
				styleFont.setFontName("宋体");
				styleFont.setFontHeightInPoints((short) 12);//设置字体大小
				specialStyle.setFont(styleFont);
				specialStyle.setAlignment(CellStyle.ALIGN_CENTER);
				specialStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				specialStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
				specialStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
				specialStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
				specialStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
				specialStyle.setWrapText(true);
				//定义：dataStyle（具体部门数据样式）
				HSSFCellStyle dataStyle = book.createCellStyle();
				styleFont = book.createFont();
				styleFont.setFontName("宋体");
				styleFont.setFontHeightInPoints((short) 12);//设置字体大小
				dataStyle.setFont(styleFont);
				dataStyle.setAlignment(CellStyle.ALIGN_CENTER);
				dataStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				dataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
				dataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
				dataStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
				dataStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
				//定义：bottomDtaStyle（底部辅助信息样式，如：填表时间等数据）
				HSSFCellStyle bottomDataStyle = book.createCellStyle();
				styleFont = book.createFont();
				styleFont.setFontName("宋体");
				styleFont.setFontHeightInPoints((short) 12);//设置字体大小
				bottomDataStyle.setFont(styleFont);
				bottomDataStyle.setAlignment(CellStyle.ALIGN_LEFT);
				bottomDataStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				
				/*
				 *从这里开始一次获取每一行，并设置每一个单元格的具体数据
				 *如果需要合并单元格，则通过RegionUtil进行合并
				 */
				//第0行
				HSSFRow row = sheet.createRow(0);
				row.setHeight((short) 900);
				HSSFCell reportNameCell = row.createCell(0);
		        reportNameCell.setCellValue(reportName);
		        reportNameCell.setCellStyle(reportNameStyle);
		        CellRangeAddress reportNameRange = new CellRangeAddress(0,0,0,tdCount - 1);
		        sheet.addMergedRegion(reportNameRange);
		        RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_NONE, reportNameRange, sheet, book);  
			    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, reportNameRange, sheet, book);  
			    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, reportNameRange, sheet, book);  
			    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, reportNameRange, sheet, book);
			    //第1行
		        row = sheet.createRow(1);
		        row.setHeight((short) 500);
		        HSSFCell orgNameCell = row.createCell(0);
		        orgNameCell.setCellValue(orgName);
		        orgNameCell.setCellStyle(orgNameStyle);
		        CellRangeAddress orgNameRange = new CellRangeAddress(1,1,0,tdCount - 1);
		        sheet.addMergedRegion(orgNameRange);
		        RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_NONE, orgNameRange, sheet, book);  
			    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, orgNameRange, sheet, book);  
			    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, orgNameRange, sheet, book);  
			    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_NONE, orgNameRange, sheet, book);
			    //第2行
		        row = sheet.createRow(2);
		        row.setHeightInPoints((float)200);
		        HSSFCell stickCell_0 = row.createCell(0);
		        HSSFCell stickCell_1 = row.createCell(1);
		        HSSFCell stickCell_2 = row.createCell(2);
		        HSSFCell stickCell_3 = row.createCell(3);
		        HSSFCell stickCell_4 = row.createCell(4);
		        HSSFCell stickCell_5 = row.createCell(5);
		        HSSFCell stickCell_6 = row.createCell(6);
		        stickCell_0.setCellValue("");
		        stickCell_1.setCellValue("已婚育龄妇女数");
		        stickCell_2.setCellValue("已婚有生育条件的育龄妇女数");
		        stickCell_3.setCellValue("已结扎人数");
		        stickCell_4.setCellValue("已上环人数");
		        stickCell_5.setCellValue("皮埋人数");
		        stickCell_6.setCellValue("持证已孕代孕人数");
		        stickCell_0.setCellStyle(stickStyle);
		        stickCell_1.setCellStyle(stickStyle);
		        stickCell_2.setCellStyle(stickStyle);
		        stickCell_3.setCellStyle(stickStyle);
		        stickCell_4.setCellStyle(stickStyle);
		        stickCell_5.setCellStyle(stickStyle);
		        stickCell_6.setCellStyle(stickStyle);
		        CellRangeAddress sticlRange_0 = new CellRangeAddress(2,4,0,0);
		        CellRangeAddress sticlRange_1 = new CellRangeAddress(2,4,1,1);
		        CellRangeAddress sticlRange_2 = new CellRangeAddress(2,4,2,2);
		        CellRangeAddress sticlRange_3 = new CellRangeAddress(2,4,3,3);
		        CellRangeAddress sticlRange_4 = new CellRangeAddress(2,4,4,4);
		        CellRangeAddress sticlRange_5 = new CellRangeAddress(2,4,5,5);
		        CellRangeAddress sticlRange_6 = new CellRangeAddress(2,4,6,6);
		        sheet.addMergedRegion(sticlRange_0);
		        sheet.addMergedRegion(sticlRange_1);
		        sheet.addMergedRegion(sticlRange_2);
		        sheet.addMergedRegion(sticlRange_3);
		        sheet.addMergedRegion(sticlRange_4);
		        sheet.addMergedRegion(sticlRange_5);
		        sheet.addMergedRegion(sticlRange_6);
		        
		        row = sheet.getRow(2);
		        row.setHeightInPoints((float)20);
		        HSSFCell specialCell_0 = row.createCell(7);
		        specialCell_0.setCellValue("使用药具人数");
		        specialCell_0.setCellStyle(specialStyle);
		        CellRangeAddress specialRangeTemp = new CellRangeAddress(2,2,7,7 + tmaTitleTdCount);
		        sheet.addMergedRegion(specialRangeTemp);
		        RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, specialRangeTemp, sheet, book);  
			    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, specialRangeTemp, sheet, book);  
			    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, specialRangeTemp, sheet, book);  
			    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, specialRangeTemp, sheet, book);
		        
		        if(kind != null && kind.size() > 0){
		        	row = sheet.createRow(3);
		        	//row.setHeightInPoints((float)30);
		        	int begin = 7;
		        	float rowHeight = 0;
		        	for(int i = 0 ; i < kind.size() ; i++){
		        		JSONObject kindObject = kind.getJSONObject(i);
		        		String kindName = URLDecoder.decode(URLDecoder.decode(kindObject.getString("name"),"UTF-8"),"UTF-8");
		        		int kindColspan = kindObject.getInt("colspan");
		        		HSSFCell specialCell_2;
		        		
		        		float nowHeight = this.getExcelCellAutoHeight(kindName,(float)(kindObject.getInt("colspan") * 2));
		        		if(nowHeight > rowHeight){
		        			rowHeight = nowHeight;
		        			row.setHeightInPoints(rowHeight);
		        		}
		        		if(i > 0){
		        			JSONObject lastKindObject = kind.getJSONObject(i - 1);
		        			begin = begin + lastKindObject.getInt("colspan");
		        			specialCell_2 = row.createCell(begin);
		        		}else{
		        			specialCell_2 = row.createCell(begin);
		        		}
		        		specialCell_2.setCellValue(kindName);
		        		specialCell_2.setCellStyle(specialStyle);
		        		specialRangeTemp = new CellRangeAddress(3,3,begin,begin + kindColspan - 1);
		        		sheet.addMergedRegion(specialRangeTemp);
		        		RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, specialRangeTemp, sheet, book);  
					    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, specialRangeTemp, sheet, book);  
					    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, specialRangeTemp, sheet, book);  
					    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, specialRangeTemp, sheet, book);
			        }
		        }
		        
		        if(tmaTitle != null && tmaTitle.size() > 0){
		        	row = sheet.createRow(4);
		        	row.setHeightInPoints((float)150);
		        	for(int i = 0 ; i < tmaTitle.size() ; i++){
		        		JSONObject tmaTitleObject = tmaTitle.getJSONObject(i);
		        		String tmaTitleName = URLDecoder.decode(URLDecoder.decode(tmaTitleObject.getString("name"),"UTF-8"),"UTF-8");
		        		HSSFCell specialCell_3 = row.createCell(7 + i);
		        		specialCell_3.setCellValue(tmaTitleName);
		        		specialCell_3.setCellStyle(specialStyle);
		        	}
		        	
		        	//合计
			        row = sheet.getRow(3);
			        HSSFCell specialCell_4 = row.createCell(7 + tmaTitle.size());
			        specialCell_4.setCellValue("合计");
			        specialCell_4.setCellStyle(specialStyle);
			        specialRangeTemp = new CellRangeAddress(3,4,7 + tmaTitle.size(),7 + tmaTitle.size());
			        sheet.addMergedRegion(specialRangeTemp);
			        RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, specialRangeTemp, sheet, book);  
				    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, specialRangeTemp, sheet, book);  
				    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, specialRangeTemp, sheet, book);  
				    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, specialRangeTemp, sheet, book);
		        }
		        
		        row = sheet.getRow(2);
		        HSSFCell stickCell_7 = row.createCell(7 + tmaTitle.size() + 1);
		        HSSFCell stickCell_8 = row.createCell(7 + tmaTitle.size() + 2);
		        stickCell_7.setCellValue("用药具有效人数");
		        stickCell_8.setCellValue("用药具有效率");
		        stickCell_7.setCellStyle(stickStyle);
		        stickCell_8.setCellStyle(stickStyle);
		        CellRangeAddress sticlRange_7 = new CellRangeAddress(2,4,7 + tmaTitle.size() + 1,7 + tmaTitle.size() + 1);
		        CellRangeAddress sticlRange_8 = new CellRangeAddress(2,4,7 + tmaTitle.size() + 2,7 + tmaTitle.size() + 2);
		        sheet.addMergedRegion(sticlRange_7);
		        sheet.addMergedRegion(sticlRange_8);
		        RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, sticlRange_7, sheet, book);  
			    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, sticlRange_7, sheet, book);  
			    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, sticlRange_7, sheet, book);  
			    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, sticlRange_7, sheet, book);
			    RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, sticlRange_8, sheet, book);  
			    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, sticlRange_8, sheet, book);  
			    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, sticlRange_8, sheet, book);  
			    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, sticlRange_8, sheet, book);
			    
			    HSSFCell orgDataCell;
	        	for(int i = 0 ; i < data.size(); i ++){
	        		row = sheet.createRow(i + 5);
	        		row.setHeightInPoints(20);
	        		JSONObject orgDataRow = data.getJSONObject(i);
	        		for(int j = 0 ; j < tdCount ; j ++){
	        			orgDataCell = row.createCell(j);
	        			if(j == 0){
	        				orgDataCell.setCellValue(URLDecoder.decode(URLDecoder.decode(orgDataRow.getString("" + j),"UTF-8"),"UTF-8"));
	        			}else if(j == tdCount - 1){
	        				orgDataCell.setCellValue(orgDataRow.getString("" + j));
	        			}else{
	        				orgDataCell.setCellValue(orgDataRow.getInt("" + j));
	        			}
	        			orgDataCell.setCellStyle(dataStyle);
	        		}
	        	}
	        	row = sheet.createRow(5 + data.size());
	        	row.setHeightInPoints(30);
		        HSSFCell bottomDataCell = row.createCell(0);
		        StringBuffer temp = new StringBuffer("注:本季最后一月出生人数").append(tmamr_thismonth_birth_nums).append("人；  离婚").append(tmamr_unmarry_num).append("人；   丧偶").append(tmamr_widowed_num).append("人；   不孕症").append(tmamr_barrenness_num).append("人；   绝经").append(tmamr_menopause_num).append("人； ");
		        bottomDataCell.setCellValue(temp.toString());
		        bottomDataCell.setCellStyle(bottomDataStyle);
		        CellRangeAddress bottomDataRange1 = new CellRangeAddress(5 + data.size(),5 + data.size(),0,tdCount - 1);
		        sheet.addMergedRegion(bottomDataRange1);
		        
		        row = sheet.createRow(6 + data.size());
		        row.setHeightInPoints(30);
		        bottomDataCell = row.createCell(0);
		        temp = new StringBuffer("单位负责人:").append(dwfzr).append("      填表人:").append(tbr).append("      填表日期:").append(tbrq_nian).append("年").append(tbrq_yue).append("月").append(tbrq_ri).append("日");
		        bottomDataCell.setCellValue(temp.toString());
		        bottomDataCell.setCellStyle(bottomDataStyle);
		        CellRangeAddress bottomDataRange2 = new CellRangeAddress(6 + data.size(),6 + data.size(),0,tdCount - 1);
		        sheet.addMergedRegion(bottomDataRange2);
		        
		        //设置合并后的单元格边框
		        //放在代码最后设置，是为了避免某些单元格单独设置HSSFCellStyle后，样式被覆盖
		        RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_NONE, bottomDataRange1, sheet, book);  
			    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, bottomDataRange1, sheet, book);  
			    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, bottomDataRange1, sheet, book);  
			    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, bottomDataRange1, sheet, book);
			    RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, bottomDataRange2, sheet, book);  
			    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, bottomDataRange2, sheet, book);  
			    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, bottomDataRange2, sheet, book);  
			    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_NONE, bottomDataRange2, sheet, book);
		        
		        RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, sticlRange_0, sheet, book);  
			    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, sticlRange_0, sheet, book);  
			    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, sticlRange_0, sheet, book);  
			    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, sticlRange_0, sheet, book);
			    RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, sticlRange_1, sheet, book);  
			    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, sticlRange_1, sheet, book);  
			    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, sticlRange_1, sheet, book);  
			    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, sticlRange_1, sheet, book);
			    RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, sticlRange_2, sheet, book);  
			    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, sticlRange_2, sheet, book);  
			    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, sticlRange_2, sheet, book);  
			    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, sticlRange_2, sheet, book);
			    RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, sticlRange_3, sheet, book);  
			    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, sticlRange_3, sheet, book);  
			    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, sticlRange_3, sheet, book);  
			    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, sticlRange_3, sheet, book);
			    RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, sticlRange_4, sheet, book);  
			    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, sticlRange_4, sheet, book);  
			    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, sticlRange_4, sheet, book);  
			    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, sticlRange_4, sheet, book);
			    RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, sticlRange_5, sheet, book);  
			    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, sticlRange_5, sheet, book);  
			    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, sticlRange_5, sheet, book);  
			    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, sticlRange_5, sheet, book);
			    RegionUtil.setBorderBottom(HSSFCellStyle.BORDER_THIN, sticlRange_6, sheet, book);  
			    RegionUtil.setBorderLeft(HSSFCellStyle.BORDER_THIN, sticlRange_6, sheet, book);  
			    RegionUtil.setBorderRight(HSSFCellStyle.BORDER_THIN, sticlRange_6, sheet, book);  
			    RegionUtil.setBorderTop(HSSFCellStyle.BORDER_THIN, sticlRange_6, sheet, book);
		        
				//输出文件并关闭流
				FileOutputStream fileOut = new FileOutputStream(file);
				book.write(fileOut);
				fileOut.close();
			} else {
				return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"报表中没有记录\"}";
			}
			
		}
		//将文件的下载地址路径返回
		return "\"" + rfile + "\"";
	}
	
	/**
	 * 根据文本数量和每行宽度动态计算excel单元格高度
	 * 主要作用是为了解决poi无法设置excel单元格高度自适应
	 * @param str
	 * 单元格文本内容
	 * @param fontCountInline
	 * 每行宽度 汉字、全角 1/每个；英文、字符等0.5/每个
	 * @return
	 * float
	 * @CreateOn Sep 13, 2012 12:30:36 PM
	 * @author bai_xin
	 */
	private float getExcelCellAutoHeight(String str, float fontCountInline) {
        float defaultRowHeight = 12.00f;//每一行的高度指定
        float defaultCount = 0.00f;
        for (int i = 0; i < str.length(); i++) {
            float ff = this.getregex(str.substring(i, i + 1));
            defaultCount = defaultCount + ff;
        }
        return ((int) (defaultCount / fontCountInline) + 1) * defaultRowHeight;//计算
    }

	/**
	 * 判断字符类型的正则方法
	 * @param charStr
	 * @return
	 * float
	 * @CreateOn Sep 13, 2012 12:30:36 PM
	 * @author bai_xin
	 */
	private float getregex(String charStr) {
        
        if(charStr==" ")
        {
            return 0.5f;
        }
        // 判断是否为字母或字符
        if (Pattern.compile("^[A-Za-z0-9]+$").matcher(charStr).matches()) {
            return 0.5f;
        }
        // 判断是否为全角

        if (Pattern.compile("[u4e00-u9fa5]+$").matcher(charStr).matches()) {
            return 1.00f;
        }
        //全角符号 及中文
        if (Pattern.compile("[^x00-xff]").matcher(charStr).matches()) {
            return 1.00f;
        }
        return 0.5f;

    }
	
	/**
	 * 撤销上报
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 */
	public Object revoke(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String quarterly = jsonObject.getString("quarterly");
		String year = jsonObject.getString("year");
		String orgid = jsonObject.getString("orgid");
		String mid = jsonObject.getString("mid");
		List<String> list = new ArrayList<String>();
		StringBuffer buf = new StringBuffer();
		buf.append("delete from tma_month_usage_report where tmamr_id = '").append(mid).append("'");
		list.add(buf.toString());
		buf.setLength(0);
		buf.append("delete from tma_quarterly_usage where TMAQU_QUARTERLY='").append(quarterly).append("' and TMAQU_TIME like '").append(year).append("%' and org_id = '").append(orgid).append("'");
		list.add(buf.toString());
		AccessDataBaseUtil.executeSQLUpdate(list);
		
		return "\"撤销成功\"";
	}
	/**
	 * 自动创建月报表
	 * @param jsonArray
	 * @return
	 * @throws Exception 
	 */
	
	public int autoCreateMonthReport(JSONObject jsonObject) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String reqdate = jsonObject.getString("reportTime");
		String mrstr = jsonObject.getString("mrArray");
		String[] mrArray = mrstr.split(",");
		String curTime = FormatUtil.getCurentDateTime();
		if("".equals(user.getUserId())){
			
		} else {
			Calendar cal;
			StringBuffer querySql = new StringBuffer();
			String reportdate;//日期
			List<?> list;
			if("".equals(reqdate)){
				cal = Calendar.getInstance();
				SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM");
				reportdate = format1.format(cal.getTime());
			}else{
				String[] strs = reqdate.split("-");
				reportdate = strs[0]+"-"+strs[1];
			}
			querySql.append("select tmamr_id,org_id,tmamr_matron_num,tmamr_women_fertility_num,tmamr_ligation_num,tmamr_sheung_wan_num,tmamr_skinburied_num,")
					.append("tmamr_keep_certificate_num,tmamr_thismonth_birth_num,tmamr_unmarry_num,tmamr_widowed_num,tmamr_barrenness_num,tmamr_menopause_num,tmamr_director,")
					.append("reserve1,reserve2,reserve3,reserve4,reserve5,create_by,create_time,last_modify_by,last_modify_time,tmamr_date from tma_month_usage_report where tmamr_date like '")
					.append(reportdate).append("%' and org_id='").append(user.getCurOrgId()).append("'");
			list = AccessDataBaseUtil.query4List(querySql.toString(), null);
			querySql.setLength(0);
			if(list.size() == 0){
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
				Calendar cal1 = Calendar.getInstance();
				String[] ss = reportdate.split("-");
				cal1.set(Calendar.YEAR, Integer.parseInt(ss[0]));
				cal1.set(Calendar.MONTH, Integer.parseInt(ss[1])-1);
				if(StringUtils.isNotBlank(reqdate)){
					cal1.set(Calendar.DATE, cal1.get(Calendar.DATE));
				}
				cal = Calendar.getInstance();
				cal.setTime(cal1.getTime());
				cal.add(Calendar.YEAR, -49);
				String mindata = format.format(cal.getTime());
				cal.setTime(cal1.getTime());
				cal.add(Calendar.YEAR, -20);
				String maxdata =format.format(cal.getTime());
				querySql.append("select ");
				/*已婚育龄妇女*/
				querySql.append("(select count(tbe_id) from tb_employee where tbe_marital_status<>'8A90F0D0176C4961B322BEB26703A320' and tbe_sex='女' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId())
						.append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as yhylfv,")
				/*已婚有生育条件育龄妇女*/
						.append("(select count(tbe_id) from tb_employee where tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')")
						.append(" and tbe_is_barrenness<>'1' and the_is_menopause<>'1' and tbe_sex='女' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId())
						.append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as yhysytjylfv,")
				/*已结扎*/
						.append("(select count(tbe_id) from tb_employee where tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
						.append(" and tbe_is_barrenness<>'1' and the_is_menopause<>'1' and tbe_sex='女' and tbe_birth_control_measures='104-1' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId()).append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as yjz,")
				/*已上环*/
						.append("(select count(tbe_id) from tb_employee where tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
						.append(" and tbe_is_barrenness<>'1' and the_is_menopause<>'1' and tbe_sex='女' and tbe_birth_control_measures='104-2' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId()).append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as ysh,")
				/*皮埋*/
						.append("(select count(tbe_id) from tb_employee where tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
						.append(" and tbe_is_barrenness<>'1' and the_is_menopause<>'1' and tbe_sex='女' and tbe_birth_control_measures='104-3' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId()).append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as pm,")
				/*持证已孕待孕*/
						.append("(select count(a.tbe_id) from tb_employee a inner join tb_family_base b on (a.tbe_id=b.tbe_id or a.tbe_id=b.tbe_spouse_id) and a.tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
						.append(" and a.tbe_is_barrenness<>'1' and a.the_is_menopause<>'1' and a.tbe_sex='女' and a.tbe_status not in ('102-3','102-4','102-5') and a.org_id in (").append(user.getCurSubOrgId()).append(") and a.tbe_birthday<='").append(maxdata).append("' and a.tbe_birthday>'").append(mindata).append("' inner join tbe_pregnancy_record c on b.tbfi_id=c.tbfi_id and (c.tbepr_option='0' or c.tbepr_option='1')) as czdydy,");
				/*本月出生*/
				if("oracle".endsWith(user.getDbType())){
					querySql.append("(select nvl(sum(t1.tbenm_gravida_num),0) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where t1.tbenm_birth_time like '").append(reportdate).append("%' and e.org_id in (").append(user.getCurSubOrgId()).append(")) as bycs,");
				}else if("mysql".endsWith(user.getDbType())){
					
					querySql.append("(select ifnull(sum(t1.tbenm_gravida_num),0) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where t1.tbenm_birth_time like '").append(reportdate).append("%' and e.org_id in (").append(user.getCurSubOrgId()).append(")) as bycs,");
				}
				/*离婚*/
				querySql.append("(select count(tbe_id) from tb_employee where tbe_sex='女' and tbe_marital_status='8A90F0D0176C4961B322BEB26703A322' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId()).append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as lh,")
				/*丧偶*/
						.append("(select count(tbe_id) from tb_employee where tbe_sex='女' and tbe_marital_status='8A90F0D0176C4961B322BEB26703A323' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId()).append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as so,")
				/*不孕症*/
						.append("(select count(tbe_id) from tb_employee where tbe_sex='女' and tbe_is_barrenness='1' and tbe_status not in ('102-3','102-4','102-5') and org_id in (").append(user.getCurSubOrgId()).append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as byz,")
				/*绝经*/
						.append("(select count(tbe_id) from tb_employee where tbe_sex='女' and the_is_menopause='1' and tbe_status not in ('102-3','102-4','102-5' ) and org_id in (").append(user.getCurSubOrgId()).append(") and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as jj");
				list = AccessDataBaseUtil.query(querySql.toString(), null);
				querySql.setLength(0);
				querySql.append("insert into tma_month_usage_report(tmamr_id,org_id,tmamr_matron_num,tmamr_women_fertility_num,")
				.append("tmamr_ligation_num,tmamr_sheung_wan_num,tmamr_skinburied_num,tmamr_keep_certificate_num,tmamr_thismonth_birth_num,")
				.append("tmamr_unmarry_num,tmamr_widowed_num,tmamr_barrenness_num,tmamr_menopause_num,tmamr_director,reserve1,reserve2,reserve3")
				.append(",reserve4,reserve5,create_by,create_time,last_modify_by,last_modify_time,tmamr_date) values(")
				.append("'").append(AccessDataBaseUtil.getPKUUID()).append("'")	
				.append(",'").append(user.getCurOrgId()).append("'")	
				.append(",'").append(mrArray[1]).append("'")	
				.append(",'").append(mrArray[2]).append("'")	
				.append(",'").append(mrArray[3]).append("'")	
				.append(",'").append(mrArray[4]).append("'")	
				.append(",'").append(mrArray[5]).append("'")	
				.append(",'").append(mrArray[6]).append("'")	
				.append(",'").append(mrArray[7]).append("'")	
				.append(",'").append(mrArray[8]).append("'")	
				.append(",'").append(mrArray[9]).append("'")	
				.append(",'").append(mrArray[10]).append("'")	
				.append(",'").append(mrArray[11]).append("'")	
				.append(",'','','','','',''")	
				.append(",'").append(user.getUserId()).append("'")	
				.append(",'").append(curTime).append("'")	
				.append(",'").append(user.getUserId()).append("'")	
				.append(",'").append(curTime).append("'")	
				.append(",'").append(reqdate).append("')");	
				AccessDataBaseUtil.executeSQLUpdate(querySql.toString());
			}else{
				querySql.append("update tma_month_usage_report set tmamr_matron_num='").append(mrArray[1]).append("',tmamr_women_fertility_num='")
				.append(mrArray[2]).append("',tmamr_ligation_num='").append(mrArray[3]).append("',tmamr_sheung_wan_num='")
				.append(mrArray[4]).append("',tmamr_skinburied_num='").append(mrArray[5]).append("',tmamr_keep_certificate_num='")
				.append(mrArray[6]).append("',tmamr_thismonth_birth_num='").append(mrArray[7]).append("',tmamr_unmarry_num='")
				.append(mrArray[8]).append("',tmamr_widowed_num='").append(mrArray[9]).append("',tmamr_barrenness_num='")
				.append(mrArray[10]).append("',tmamr_menopause_num='").append(mrArray[11]).append("',last_modify_by='")
				.append(user.getUserId()).append("',last_modify_time='").append(curTime).append("',tmamr_date='").append(reqdate)
				.append("' where tmamr_id='").append(mrArray[0]).append("'");
				AccessDataBaseUtil.executeSQLUpdate(querySql.toString());
			}
		}
		return 1;
	}
	public void autoReport(List<String> params,String orgid) throws Exception{
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		String curTime = FormatUtil.getCurentDateTime();
		StringBuffer buf = new StringBuffer();
		buf.append("select tmaqu_id from tma_quarterly_usage where TMAQU_QUARTERLY='").append(params.get(1)).append("' and TMAQU_TIME like '").append(params.get(0).split("-")[0])
		.append("%' and org_id='").append(orgid).append("'");
		List<?> list = AccessDataBaseUtil.query4List(buf.toString(),null);
		List<String> sqls = new ArrayList<String>();
		if(list == null || list.size() == 0){
			buf.setLength(0);
			buf.append("select department_id from t_department where dparent_id = '").append(orgid).append("'");
			List<?> orgids = AccessDataBaseUtil.query4List(buf.toString(), null);
			buf.setLength(0);
			if(orgids != null && orgids.size() > 0){
				for(int i = 0; i < orgids.size(); i++){
					autoReport(params,orgids.get(i).toString());
				}
				String ids = formatString(orgids);
				buf.append("select sum(tmamr_matron_num),sum(tmamr_women_fertility_num),sum(tmamr_ligation_num),sum(tmamr_sheung_wan_num),sum(tmamr_skinburied_num),")
				.append("sum(tmamr_keep_certificate_num),sum(tmamr_thismonth_birth_num),sum(tmamr_unmarry_num),sum(tmamr_widowed_num),sum(tmamr_barrenness_num),sum(tmamr_menopause_num)")
				.append(" from tma_month_usage_report where tmamr_date like '")
				.append(params.get(4)).append("%' and org_id in('").append(ids).append("')");
				list = AccessDataBaseUtil.query(buf.toString(), null);
				Object[] o;
				o = (Object[])list.get(0);
				buf.setLength(0);
				buf.append("insert into tma_month_usage_report(tmamr_id,org_id,tmamr_matron_num,tmamr_women_fertility_num,")
				.append("tmamr_ligation_num,tmamr_sheung_wan_num,tmamr_skinburied_num,tmamr_keep_certificate_num,tmamr_thismonth_birth_num,")
				.append("tmamr_unmarry_num,tmamr_widowed_num,tmamr_barrenness_num,tmamr_menopause_num,tmamr_director,reserve1,reserve2,reserve3")
				.append(",reserve4,reserve5,create_by,create_time,last_modify_by,last_modify_time,tmamr_date) values(")
				.append("'").append(AccessDataBaseUtil.getPKUUID()).append("'")	
				.append(",'").append(orgid).append("'")	
				.append(",'").append(o[0]).append("'")	
				.append(",'").append(o[1]).append("'")	
				.append(",'").append(o[2]).append("'")	
				.append(",'").append(o[3]).append("'")	
				.append(",'").append(o[4]).append("'")	
				.append(",'").append(o[5]).append("'")	
				.append(",'").append(o[6]).append("'")	
				.append(",'").append(o[7]).append("'")	
				.append(",'").append(o[8]).append("'")	
				.append(",'").append(o[9]).append("'")	
				.append(",'").append(o[10]).append("'")	
				.append(",'','','','','',''")	
				.append(",'").append(user.getUserId()).append("'")	
				.append(",'").append(curTime).append("'")	
				.append(",'").append(user.getUserId()).append("'")	
				.append(",'").append(curTime).append("'")	
				.append(",'").append(params.get(0)).append("')");	
				sqls.add(buf.toString());
				buf.setLength(0);
				buf.append("select tmad_id,tmad_kind,sum(tmaqu_peonum) from tma_quarterly_usage where TMAQU_QUARTERLY='").append(params.get(1)).append("' and TMAQU_TIME like '").append(params.get(0).split("-")[0])
				.append("%' and org_id in ('").append(ids).append("') group by tmad_id,tmad_kind");
				list = AccessDataBaseUtil.query(buf.toString(), null);
				Object[] objs;
				for(int j = 0; j < list.size(); j++){
					objs = (Object[])list.get(j);
					buf.setLength(0);
					buf.append("insert into tma_quarterly_usage(TMAQU_ID,ORG_ID,TMAD_ID,TMAD_KIND,TMAQU_PEONUM,TMAQU_TIME,TMAQU_QUARTERLY,RESERVE1,")
					.append("RESERVE2,RESERVE3,RESERVE4,RESERVE5,CREATE_BY,CREATE_TIME,LAST_MODIFY_BY,LAST_MODIFY_TIME)")
					.append("values('").append(AccessDataBaseUtil.getPKUUID()).append("','").append(orgid)
					.append("','").append(objs[0]).append("','").append(objs[1]).append("','")
					.append(objs[2]).append("','").append(params.get(0)).append("','").append(params.get(1)).append("','','','','','','")
					.append(user.getUserId()).append("','").append(curTime).append("','").append(user.getUserId()).append("','")
					.append(curTime).append("')");
					sqls.add(buf.toString());
				}
				AccessDataBaseUtil.executeSQLUpdate(sqls);
			}else{
				createReport(params,orgid);
			}
		}
	}
	public String formatString(List<?> list) throws Exception{
		StringBuffer b = new StringBuffer();
		if(list != null){
			b.append(list.get(0));
			for(int i = 1; i < list.size(); i++){
				b.append("','").append(list.get(i));
			}	
		}
		return b.toString();
	}
	/**
	 * 自动创建月报表
	 * @param jsonArray
	 * @return
	 * @throws Exception 
	 */
	
	public void createReport(List<String> params,String orgid) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		List<String> sqls = new ArrayList<String>();
		String curTime = FormatUtil.getCurentDateTime();
		Calendar cal;
		StringBuffer querySql = new StringBuffer();
		String reportdate;//日期
		List<?> list;
		String[] strs = params.get(0).split("-");
		reportdate = strs[0]+"-"+strs[1];
		querySql.append("select tmamr_id,org_id,tmamr_matron_num,tmamr_women_fertility_num,tmamr_ligation_num,tmamr_sheung_wan_num,tmamr_skinburied_num,")
				.append("tmamr_keep_certificate_num,tmamr_thismonth_birth_num,tmamr_unmarry_num,tmamr_widowed_num,tmamr_barrenness_num,tmamr_menopause_num,tmamr_director,")
				.append("reserve1,reserve2,reserve3,reserve4,reserve5,create_by,create_time,last_modify_by,last_modify_time,tmamr_date from tma_month_usage_report where tmamr_date like '")
				.append(reportdate).append("%' and org_id='").append(orgid).append("'");
		list = AccessDataBaseUtil.query4List(querySql.toString(), null);
		querySql.setLength(0);
		if(list.size() == 0){
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
			Calendar cal1 = Calendar.getInstance();
			String[] ss = reportdate.split("-");
			cal1.set(Calendar.YEAR, Integer.parseInt(ss[0]));
			cal1.set(Calendar.MONTH, Integer.parseInt(ss[1])-1);
			if(StringUtils.isNotBlank(params.get(0))){
				cal1.set(Calendar.DATE, cal1.get(Calendar.DATE));
			}
			cal = Calendar.getInstance();
			cal.setTime(cal1.getTime());
			cal.add(Calendar.YEAR, -49);
			String mindata = format.format(cal.getTime());
			cal.setTime(cal1.getTime());
			cal.add(Calendar.YEAR, -20);
			String maxdata =format.format(cal.getTime());
			querySql.append("select ");
			/*已婚育龄妇女*/
			querySql.append("(select count(tbe_id) from tb_employee where tbe_marital_status<>'8A90F0D0176C4961B322BEB26703A320' and tbe_sex='女' and tbe_status not in ('102-3','102-4','102-5') and org_id ='").append(orgid)
					.append("' and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as yhylfv,")
			/*已婚有生育条件育龄妇女*/
					.append("(select count(tbe_id) from tb_employee where tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')")
					.append(" and tbe_is_barrenness<>'1' and the_is_menopause<>'1' and tbe_sex='女' and tbe_status not in ('102-3','102-4','102-5') and org_id ='").append(orgid)
					.append("' and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as yhysytjylfv,")
			/*已结扎*/
					.append("(select count(tbe_id) from tb_employee where tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
					.append(" and tbe_is_barrenness<>'1' and the_is_menopause<>'1' and tbe_sex='女' and tbe_birth_control_measures='104-1' and tbe_status not in ('102-3','102-4','102-5') and org_id ='").append(orgid).append("' and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as yjz,")
			/*已上环*/
					.append("(select count(tbe_id) from tb_employee where tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
					.append(" and tbe_is_barrenness<>'1' and the_is_menopause<>'1' and tbe_sex='女' and tbe_birth_control_measures='104-2' and tbe_status not in ('102-3','102-4','102-5') and org_id ='").append(orgid).append("' and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as ysh,")
			/*皮埋*/
					.append("(select count(tbe_id) from tb_employee where tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
					.append(" and tbe_is_barrenness<>'1' and the_is_menopause<>'1' and tbe_sex='女' and tbe_birth_control_measures='104-3' and tbe_status not in ('102-3','102-4','102-5') and org_id ='").append(orgid).append("' and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as pm,")
			/*持证已孕待孕*/
					.append("(select count(a.tbe_id) from tb_employee a inner join tb_family_base b on (a.tbe_id=b.tbe_id or a.tbe_id=b.tbe_spouse_id) and a.tbe_marital_status not in ('8A90F0D0176C4961B322BEB26703A320','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323')") 
					.append(" and a.tbe_is_barrenness<>'1' and a.the_is_menopause<>'1' and a.tbe_sex='女' and a.tbe_status not in ('102-3','102-4','102-5') and a.org_id ='").append(orgid).append("' and a.tbe_birthday<='").append(maxdata).append("' and a.tbe_birthday>'").append(mindata).append("' inner join tbe_pregnancy_record c on b.tbfi_id=c.tbfi_id and (c.tbepr_option='0' or c.tbepr_option='1')) as czdydy,");
			/*本月出生*/
			if("oracle".endsWith(user.getDbType())){
				querySql.append("(select nvl(sum(t1.tbenm_gravida_num),0) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where t1.tbenm_birth_time like '").append(reportdate).append("%' and e.org_id ='").append(orgid).append("') as bycs,");
			}else if("mysql".endsWith(user.getDbType())){
				
				querySql.append("(select ifnull(sum(t1.tbenm_gravida_num),0) from tbe_neonate_main t1 left join tbe_pregnancy_record rec on rec.tbepr_id=t1.tbepr_id left join tb_family_base f on f.tbfi_id=rec.tbfi_id left join tb_employee e on e.tbe_id=f.tbe_id where t1.tbenm_birth_time like '").append(reportdate).append("%' and e.org_id ='").append(orgid).append("') as bycs,");
			}
			/*离婚*/
			querySql.append("(select count(tbe_id) from tb_employee where tbe_sex='女' and tbe_marital_status='8A90F0D0176C4961B322BEB26703A322' and tbe_status not in ('102-3','102-4','102-5') and org_id ='").append(orgid).append("' and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as lh,")
			/*丧偶*/
					.append("(select count(tbe_id) from tb_employee where tbe_sex='女' and tbe_marital_status='8A90F0D0176C4961B322BEB26703A323' and tbe_status not in ('102-3','102-4','102-5') and org_id ='").append(orgid).append("' and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as so,")
			/*不孕症*/
					.append("(select count(tbe_id) from tb_employee where tbe_sex='女' and tbe_is_barrenness='1' and tbe_status not in ('102-3','102-4','102-5') and org_id ='").append(orgid).append("' and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as byz,")
			/*绝经*/
					.append("(select count(tbe_id) from tb_employee where tbe_sex='女' and the_is_menopause='1' and tbe_status not in ('102-3','102-4','102-5' ) and org_id ='").append(orgid).append("' and tbe_birthday<='").append(maxdata).append("' and tbe_birthday>'").append(mindata).append("') as jj");
			list = AccessDataBaseUtil.query(querySql.toString(), null);
			Object[] o;
			o = (Object[])list.get(0);
			querySql.setLength(0);
			querySql.append("insert into tma_month_usage_report(tmamr_id,org_id,tmamr_matron_num,tmamr_women_fertility_num,")
			.append("tmamr_ligation_num,tmamr_sheung_wan_num,tmamr_skinburied_num,tmamr_keep_certificate_num,tmamr_thismonth_birth_num,")
			.append("tmamr_unmarry_num,tmamr_widowed_num,tmamr_barrenness_num,tmamr_menopause_num,tmamr_director,reserve1,reserve2,reserve3")
			.append(",reserve4,reserve5,create_by,create_time,last_modify_by,last_modify_time,tmamr_date) values(")
			.append("'").append(AccessDataBaseUtil.getPKUUID()).append("'")	
			.append(",'").append(orgid).append("'")	
			.append(",'").append(o[0]).append("'")	
			.append(",'").append(o[1]).append("'")	
			.append(",'").append(o[2]).append("'")	
			.append(",'").append(o[3]).append("'")	
			.append(",'").append(o[4]).append("'")	
			.append(",'").append(o[5]).append("'")	
			.append(",'").append(o[6]).append("'")	
			.append(",'").append(o[7]).append("'")	
			.append(",'").append(o[8]).append("'")	
			.append(",'").append(o[9]).append("'")	
			.append(",'").append(o[10]).append("'")		
			.append(",'','','','','',''")	
			.append(",'").append(user.getUserId()).append("'")	
			.append(",'").append(curTime).append("'")	
			.append(",'").append(user.getUserId()).append("'")	
			.append(",'").append(curTime).append("'")	
			.append(",'").append(params.get(0)).append("')");	
			sqls.add(querySql.toString());
		}
		querySql.setLength(0);
		querySql.append("select a.tmad_id,a.tmad_kind,count(e.name)")
		.append(" from tma_dictionary a left join (select b.tmad_id as name from tma_provide_detail b inner join tma_provide_main c on b.tmapm_id = c.tmapm_id and c.org_id = '")
		.append(orgid).append("' and c.tmapm_time >= '").append(params.get(2)).append("' and c.tmapm_time < '").append(params.get(3)).append("' and c.reserve2 = '1' group by b.tmad_id,c.tbe_id ) e on a.tmad_id = e.name where a.tmad_kind not in('105-1','105-3','105-4','105-5') and a.tmad_status='0' and a.reserve1='1' group by a.tmad_id,a.tmad_kind order by a.tmad_kind desc,a.tmad_id");
		list = AccessDataBaseUtil.query(querySql.toString(), null);
		Object[] objs;
		for(int j = 0; j < list.size(); j++){
			objs = (Object[])list.get(j);
			querySql.setLength(0);
			querySql.append("insert into tma_quarterly_usage(TMAQU_ID,ORG_ID,TMAD_ID,TMAD_KIND,TMAQU_PEONUM,TMAQU_TIME,TMAQU_QUARTERLY,RESERVE1,")
			.append("RESERVE2,RESERVE3,RESERVE4,RESERVE5,CREATE_BY,CREATE_TIME,LAST_MODIFY_BY,LAST_MODIFY_TIME)")
			.append("values('").append(AccessDataBaseUtil.getPKUUID()).append("','").append(orgid)
			.append("','").append(objs[0]).append("','").append(objs[1]).append("','")
			.append(objs[2]).append("','").append(params.get(0)).append("','").append(params.get(1)).append("','','','','','','")
			.append(user.getUserId()).append("','").append(curTime).append("','").append(user.getUserId()).append("','")
			.append(curTime).append("')");
			sqls.add(querySql.toString());
		}
		AccessDataBaseUtil.executeSQLUpdate(sqls);
	}
	/**
	 * 一键上报
	 * @param jsonArray
	 * @return
	 * @throws Exception 
	 */
	
	public Object oneKeyReport(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		List<String> params = new ArrayList<String>();
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String qua = jsonObject.getString("quarterly");//季度
		String year = jsonObject.getString("year");//年度
		Calendar cal = Calendar.getInstance();
		String rdate = "";
		int d = cal.get(Calendar.DATE);
		if("一".equals(qua)){
			rdate = year+"-03-"+d;
		}else if("二".equals(qua)){
			rdate = year+"-06-"+d;
		}else if("三".equals(qua)){
			rdate = year+"-09-"+d;
		}else{
			rdate = year+"-12-"+d;
		}
		String maxdate = null;
		String mindate = null;
		String yymm = null;
		String orgid = user.getCurOrgId(); 
		String[] arr = rdate.split("-");
		int yearTemp = Integer.parseInt(year);
		if("一".equals(qua)){
			mindate = yearTemp + "-01";
			maxdate = yearTemp + "-04";
		}else if("二".equals(qua)){
			mindate = yearTemp + "-04";
			maxdate = yearTemp + "-07";
		}else if("三".equals(qua)){
			mindate = yearTemp + "-07";
			maxdate = yearTemp + "-10";
		}else if("四".equals(qua)){
			mindate = yearTemp + "-10";
			maxdate = (yearTemp + 1) + "-01";
		}
		yymm = arr[0]+"-"+arr[1];
		params.add(rdate);
		params.add(qua);
		params.add(mindate);
		params.add(maxdate);
		params.add(yymm);
		autoReport(params, orgid);
		return "\"保存成功\"";
	}

	/**
	 * 提醒未上报部门
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 */
	public Object alert(Object jsonArray) throws Exception {
		//获取当前用户对象
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		//获取logicdata
		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String year = jsonObject.getString("year");
			String quarter = jsonObject.getString("quarter");
			int type = jsonObject.getInt("type");
			String announcement = URLDecoder.decode(URLDecoder.decode(jsonObject.getString("announcement"),"UTF-8"),"UTF-8");
			String ids = jsonObject.getString("ids");
			String[] unsubmitIds = ids.split(",");
			int atime = jsonObject.getInt("atime");
			int ntime = jsonObject.getInt("ntime");
			
			StringBuffer notice = new StringBuffer("一汽计生办提醒您，尽快上报").append(year).append("年");
			notice.append("第").append(quarter).append("季度药具使用发放情况报表");
			if(ids != null && unsubmitIds.length > 0){
				String tmaid = this.sendAnnouncement(user, announcement, type + "-" + year + "-" + quarter, 2, atime);
				this.sendNotice(user, unsubmitIds, notice.toString(), tmaid, type + "-" + year + "-" + quarter, ntime);
			}else{
				return "{\"status\":\"failure\",\"message\":\"通知和公告发送失败， 请刷新页面重试，或联系技术支持人员！\"}";
			}
			return "{\"status\":\"success\",\"message\":\"通知和公告发送成功！\"}";
		}else{
			return "{\"status\":\"failure\",\"message\":\"通知和公告发送失败， 请刷新页面重试，或联系技术支持人员！\"}";
		}
	}

	/**
	 * 发布公告
	 * @param user
	 * 公告发布人（UserBean实例）
	 * @param content
	 * 公告内容
	 * @param traceKey
	 * 公告追溯键值，用来标识该通知属于哪个模块、哪个功能等信息
	 * @param type
	 * 公告类型：1单独公告；2关联公告
	 * @param time
	 * 公告持续时间（单位小时），为0则表示永久有效，此时需要程序控制公告失效状态
	 * @return
	 * 公告ID
	 * @throws Exception
	 */
	private String sendAnnouncement(UserBean user, String content, String traceKey, int type, int time) throws Exception{
		StringBuffer sql = new StringBuffer();
		String tmaid = AccessDataBaseUtil.getPKUUID();
		sql.append("insert into TM_ANNOUNCEMENT (TMA_ID,TMA_FROM,TMA_CONTENT,TMA_STATUS,TMA_TYPE,TMA_TIME,TMA_TRACE_KEY,CREATE_BY,CREATE_TIME) ");
		sql.append("values (");
		sql.append("'").append(tmaid).append("',");
		sql.append("'").append(user.getCurOrgId()).append("',");
		sql.append("'").append(content).append("',");
		sql.append("1").append(",");
		sql.append(type).append(",");
		sql.append(time).append(",");
		sql.append("'").append(traceKey).append("',");
		sql.append("'").append(user.getUserId()).append("',");
		sql.append("'").append(FormatUtil.getCurentDateTime()).append("'");
		sql.append(")");
		
		AccessDataBaseUtil.executeSQLUpdate(sql.toString());
		return tmaid;
	}
	
	/**
	 * 发布通知
	 * @param user
	 * 当前操作用户的UserBean
	 * @param toOrgs
	 * 通知接收部门ID列表（List<String>）
	 * @param content
	 * 通知内容
	 * @param tmaid
	 * 关联公告ID，无关联则写0
	 * @param traceKey
	 * 通知追溯键值，用来标识该通知属于哪个模块、哪个功能等信息
	 * @param time
	 * 通知持续时间（单位小时），为0则表示只显示一次
	 * @throws Exception
	 */
	private void sendNotice(UserBean user, String[] toOrgs, String content, String tmaid, String traceKey, int time) throws Exception{
		List<String> sqlList = new ArrayList<String>();
		for(String to : toOrgs) {
			StringBuffer sql = new StringBuffer();
			sql.append("insert into TM_NOTICE (TMN_ID,TMN_FROM,TMN_TO,TMN_CONTENT,TMN_STATUS,TMN_TIME,TMN_TRACE_KEY,TMA_ID,CREATE_BY,CREATE_TIME) ");
			sql.append("values (");
			sql.append("'").append(AccessDataBaseUtil.getPKUUID()).append("',");
			sql.append("'").append(user.getCurOrgId()).append("',");
			sql.append("'").append(to).append("',");
			sql.append("'").append(content).append("',");
			sql.append("1").append(",");
			sql.append(time).append(",");
			sql.append("'").append(traceKey).append("',");
			sql.append("'").append(tmaid).append("',");
			sql.append("'").append(user.getUserId()).append("',");
			sql.append("'").append(FormatUtil.getCurentDateTime()).append("'");
			sql.append(")");
			
			sqlList.add(sql.toString());
		}
		AccessDataBaseUtil.executeSQLUpdate(sqlList);
	}
}


